﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace App_1Mark1
{
    public partial class frm_G_Absence : Form
    {
        SqlDataAdapter sqlDA = new SqlDataAdapter();
        SqlCommand sqlCom = new SqlCommand();
        SqlConnection sqlC = new SqlConnection();
        SqlCommandBuilder sqlCB = new SqlCommandBuilder();
        DataSet DS = new DataSet();

        public frm_G_Absence()
        {
            InitializeComponent();
        }

        private void frm_G_Absence_Load(object sender, EventArgs e)
        {
            cbYear.SelectedIndexChanged -= cbYear_SelectedIndexChanged;
            //cbYear.Items.Add("2016");
            //cbYear.Items.Add("2017");
            cbYear.SelectedIndex = 0;
            dtpHA.CustomFormat = "hh:mm";
            dtpHA.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
            dtpHA.ShowUpDown = true;
            dtpDA.CustomFormat = "dd/MM/yyyy";
            dtpDA.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
            dtpDA.ShowUpDown = true;
            sqlCom.Connection = sqlC;
            sqlC.ConnectionString = "Data Source=DESKTOP-03S1896;Initial Catalog=Gestion_Scolarité;Integrated Security=True";
            sqlC.Open();
            fillGrid();
            cbYear.SelectedIndexChanged += cbYear_SelectedIndexChanged;
        }

        void fillGrid()
        {
            sqlCom.CommandText = "select E.CNE, Nom_ET, Prenom_ET, Date_Seance, heure_seance from Etudiant E inner join Sabsenter S on E.CNE = S.CNE inner join Seance SS on SS.ID_Seance = S.ID_Seance inner join INSC_ET II on II.CNE = E.CNE inner join Inscription I on I.ID_Ins = II.ID_Ins where I.Annee_Scolaire = '" + cbYear.SelectedItem.ToString()+"'";
            sqlDA.SelectCommand = sqlCom;
            sqlDA.Fill(DS, "Absence");
            dgvA.DataSource = DS.Tables["Absence"];
            dgvA.Columns[1].HeaderText = "Nom";
            dgvA.Columns[2].HeaderText = "Prenom";
            dgvA.Columns[3].HeaderText = "Date";
            dgvA.Columns[4].HeaderText = "Heure";
        }

        private void lblOK_Click(object sender, EventArgs e)
        {
            sqlCom.CommandText = "select ID_Seance from Seance where Date_Seance = '" + dtpDA.Value.Month.ToString() + "/" + dtpDA.Value.Day.ToString() + "/" + dtpDA.Value.Year.ToString() + "' and heure_seance = '" + dtpHA.Value.Hour.ToString() + ":" + dtpHA.Value.Minute.ToString() + "'";
            int id_Seance = Convert.ToInt16(sqlCom.ExecuteScalar());
            if (id_Seance == 0)
                MessageBox.Show("Seance Does Not Exist");
            else
            {
                if (txtCNE.Text != "")
                {
                    sqlCom.CommandText = "insert into Sabsenter values(" + id_Seance + ", '" + txtCNE.Text + "')";
                    sqlCom.ExecuteNonQuery();
                    DS.Tables["Absence"].Clear();
                    fillGrid();
                }
                else
                    MessageBox.Show("CNE Can Not Be Empty");
            }
        }

        private void cbYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            DS.Tables["Absence"].Clear();
            fillGrid();
        }
    }
}
